/**
 * 
 */
package com.csc.empmngt.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.transaction.annotation.Transactional;

import com.csc.empmngt.dao.EmployeeDAO;
import com.csc.empmngt.dto.EmployeeDTO;

/**
 * Define all Data access operations of employee
 * @author angelkid
 *
 */
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO {
	
	private static final String SQL_READ_ALL = "SELECT * FROM EMPLOYEE";
	private static final String SQL_READ = "SELECT * FROM EMPLOYEE WHERE ID = :id";
	private static final String SQL_CREATE = "INSERT INTO EMPLOYEE " +
			"(FIRST_NAME,	LAST_NAME,	BIRTH_DAY,	EMAIL) VALUES" +
			"(:firstName,	:lastName,	:birthDay,	:email);";

	private static final String SQL_UPDATE = "UPDATE EMPLOYEE " +
			"SET FIRST_NAME=:firstName,LAST_NAME=:lastName,BIRTH_DAY=:birthDay,EMAIL=:email " +
			"WHERE ID=:id;";
	
	private static final String SQL_DELETE = "DELETE FROM EMPLOYEE WHERE ID=:id;";
	
	private NamedParameterJdbcTemplate jdbcTemplate;
	
	/* (non-Javadoc)
	 * @see com.csc.empmngt.dao.EmployeeDAO#findAll()
	 */
	@Transactional(readOnly = true)
	public List<EmployeeDTO> findAll() {
		return jdbcTemplate.query(SQL_READ_ALL, new EmployeeDTORowMapper());
	}

	/* (non-Javadoc)
	 * @see com.csc.empmngt.dao.EmployeeDAO#create(com.csc.empmngt.dto.EmployeeDTO)
	 */
	public long create(EmployeeDTO employeeDTO) {
		BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(employeeDTO);
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(SQL_CREATE, parameterSource,generatedKeyHolder);
		employeeDTO.setId(generatedKeyHolder.getKey().longValue());
		return employeeDTO.getId();
	}

	/* (non-Javadoc)
	 * @see com.csc.empmngt.dao.EmployeeDAO#read(long)
	 */
	@Transactional(readOnly = true)
	public EmployeeDTO read(long id) {
		SqlParameterSource paramSource = new MapSqlParameterSource("id",id);
		try{
			return jdbcTemplate.queryForObject(SQL_READ,
					paramSource,
					new EmployeeDTORowMapper());
			
		} catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	/* (non-Javadoc)
	 * @see com.csc.empmngt.dao.EmployeeDAO#update(com.csc.empmngt.dto.EmployeeDTO)
	 */
	public void update(EmployeeDTO employeeDTO) {
		BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(employeeDTO);
		jdbcTemplate.update(SQL_UPDATE, parameterSource);
	}

	/* (non-Javadoc)
	 * @see com.csc.empmngt.dao.EmployeeDAO#delete(long)
	 */
	public long delete(long id) {
		SqlParameterSource parameterSource = new MapSqlParameterSource("id",id);
		return jdbcTemplate.update(SQL_DELETE, parameterSource);
	}

	public NamedParameterJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	private static class EmployeeDTORowMapper implements RowMapper<EmployeeDTO>{

		public EmployeeDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
			EmployeeDTO employeeDTO = new EmployeeDTO();
			employeeDTO.setId(rs.getLong("ID"));
			employeeDTO.setLastModified(rs.getTimestamp("LAST_MODIFIED"));
			employeeDTO.setFirstName(rs.getString("FIRST_NAME"));
			employeeDTO.setLastName(rs.getString("LAST_NAME"));
			employeeDTO.setEmail(rs.getString("EMAIL"));
			employeeDTO.setBirthDay(rs.getDate("BIRTH_DAY"));
			return employeeDTO;
		}
		
	}
}
